//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//*********************************************************

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Xml;
using System.IO;
using System.Runtime.Serialization;


namespace Microsoft.Research.DataLayer
{
    /// <summary>
    /// Abstracts the SQL Server database running as storage backend
    /// </summary>
    /// <seealso cref="Connection"/>
    /// <seealso cref="IConnectionAtomicity"/>
    [Serializable]
    public partial class SQLConnectionWorker : CodeBehind, IConnectionAtomicity, IConnectionProviderInfo, IConnectionSecureWorker, IStorageSecurity , ISerializable
    {
        private const string CurrentVersion = "1.0.0.0";

        private const string SPCreateUser = "CreateUser";
        private const string SPCreateLogin = "CreateLogin";
        private const string ConnectionString = "ConnectionString";

        private const string SPAddrolemember = "sp_addrolemember";
        private const string SPDroprolemember = "sp_droprolemember";
        private const string DBOwner = "db_owner";

        private const string TemplateXML = "<?xml version=\"1.0\" encoding=\"utf-8\" ?><DataLayout name=\"CodeGen\"><Objects/></DataLayout>";
        private const string CmdString = "@echo off\n SET DBNAME=%1\n if \"%2\" == \"/all\" call run /table-p1 /table-p2 /relation /view-count /view-base /sp-add /sp-delete /sp-update /sp-count /sp-search /sp-get /sp-getbyid\n if \"%2\" == \"/table-p1\" for %%i in (create_table_p1_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/table-p2\" for %%i in (create_table_p2_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/relation\" for %%i in (create_relation_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/view-count\" for %%i in (create_view_count_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/view-base\" for %%i in (create_view_base_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/sp-add\" for %%i in (create_sp_add_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/sp-delete\" for %%i in (create_sp_delete_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/sp-update\" for %%i in (create_sp_update_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/sp-count\" for %%i in (create_sp_count_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/sp-search\" for %%i in (create_sp_search_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/sp-get\" for %%i in (create_sp_get_*) do osql -E -d %DBNAME% -i %%i \n if \"%2\" == \"/sp-getbyid\" for %%i in (create_sp_getbyid_*) do osql -E -d %DBNAME% -i %%i";

        private string connString;
        private SqlConnection conn;

        private Dictionary<Parameter.ParamType, SqlDbType> paramTypeMap;
        private Dictionary<Parameter.ParamDirection, ParameterDirection> paramDirMap;
        private Dictionary<Parameter.ParamType, string> xmlTypeMap;

        public ConnectionSecure ConnectionSecure { get; set; }

    
        private SQLConnectionWorker()
        {
            InitTypes();
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connString">User provided connection string to bind database</param>
        private SQLConnectionWorker(string connString)
        {
            this.connString = connString;

            InitTypes();
        }

        /// <summary>
        /// Returns information about the connection
        /// </summary>
        public override string DisplayInfo
        {
            get
            {
                return string.Format("SQL Server [{0}]", this.connString);
            }
        }

        public static Connection Create(SQLConnectionParameters connInfo)
        {
            string serverName;
            bool trustedConn;
            bool userPass;
            string username;
            string password;
            bool encrypted;
            string database;

            connInfo.Load(out serverName, out trustedConn, out userPass, out username, out password,
                out encrypted, out database);

            if (trustedConn)
                return SQLConnectionWorker.CreateTrusted(serverName, database);
            else
                return SQLConnectionWorker.CreateUserPass(serverName, username, password, database);
        }

        /// <summary>
        /// Creates a trusted connection to SQL server
        /// </summary>
        /// <param name="server"></param>
        /// <param name="database"></param>
        /// <returns></returns>
        public static Connection CreateTrusted(string server, string database)
        {
            SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder();
            connString.IntegratedSecurity = true;
            connString.DataSource = server;
            connString.InitialCatalog = database;

            return new SQLConnectionWorker(connString.ConnectionString);
        }

        /// <summary>
        /// Creates a standard security connection to SQL server
        /// </summary>
        /// <param name="server"></param>
        /// <param name="user"></param>
        /// <param name="pass"></param>
        /// <param name="database"></param>
        /// <returns></returns>
        public static Connection CreateUserPass(string server, string user, string pass, string database)
        {
            SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder();
            connString.IntegratedSecurity = false;
            connString.UserID = user;
            connString.Password = pass;
            connString.DataSource = server;
            connString.InitialCatalog = database;

            return new SQLConnectionWorker(connString.ConnectionString);
        }

        /// <summary>
        /// Initializes the connection with the database
        /// </summary>
        public override void Open()
        {
            conn = new SqlConnection(connString);

            try
            {
                conn.Open();
            }
            catch (SqlException e)
            {
                throw new ConnectionFailure("Cannot connect to SQL backend", e);
            }
        }

        /// <summary>
        /// Indicates if the connection is active
        /// </summary>
        public override bool Alive
        {
            get { return conn.State == ConnectionState.Open; }
        }

        /// <summary>
        /// Closes the connection with the database
        /// </summary>
        public override void Close()
        {
            conn.Close();
        }

        /// <summary>
        /// Initialize SQL specific types and maps
        /// </summary>
        private void InitTypes()
        {
            paramTypeMap = new Dictionary<Parameter.ParamType, SqlDbType>();
            paramTypeMap.Add(Parameter.ParamType.String, SqlDbType.VarChar);
            paramTypeMap.Add(Parameter.ParamType.Guid, SqlDbType.UniqueIdentifier);
            paramTypeMap.Add(Parameter.ParamType.Int, SqlDbType.Int);
            paramTypeMap.Add(Parameter.ParamType.DateTime, SqlDbType.DateTime);
            paramTypeMap.Add(Parameter.ParamType.Text, SqlDbType.NText);
            paramTypeMap.Add(Parameter.ParamType.Boolean, SqlDbType.Bit);
            paramTypeMap.Add(Parameter.ParamType.Color, SqlDbType.Int);

            paramDirMap = new Dictionary<Parameter.ParamDirection, ParameterDirection>();
            paramDirMap.Add(Parameter.ParamDirection.In, ParameterDirection.Input);
            paramDirMap.Add(Parameter.ParamDirection.Out, ParameterDirection.Output);

            xmlTypeMap = new Dictionary<Parameter.ParamType, string>();
            xmlTypeMap.Add(Parameter.ParamType.String, "string");
            xmlTypeMap.Add(Parameter.ParamType.Guid, "guid");
            xmlTypeMap.Add(Parameter.ParamType.Int, "integer");
            xmlTypeMap.Add(Parameter.ParamType.DateTime, "dateTime");
            xmlTypeMap.Add(Parameter.ParamType.Text, "text");
            xmlTypeMap.Add(Parameter.ParamType.Boolean, "boolean");
        }

        /// <summary>
        /// Creates a new connection directed to the same SQL storage
        /// </summary>
        /// <returns></returns>
        public override Connection Clone()
        {
            Connection newConn = new SQLConnectionWorker(this.connString);
            return newConn;
        }

        /// <summary>
        /// Implements Connection.Relation
        /// </summary>
        /// <param name="action">Action to perform</param>
        /// <param name="obj1">First object in the relation</param>
        /// <param name="obj2">Second object in the relation</param>
        public override void Relation(Action action, IObject obj1, IObject obj2)
        {
            string tableName = ObjectKind(obj1.ObjType, obj2.ObjType);
            Storage type1 = obj1.ObjType;
            Storage type2 = obj2.ObjType;

            string name = string.Format("{0}_{1}", tableName, action.ToString());

            //
            // Adjust to SQL standards
            //
            Parameter id1 = new Parameter(null, Parameter.ParamType.Guid, Parameter.ParamDirection.In);
            Parameter id2 = new Parameter(null, Parameter.ParamType.Guid, Parameter.ParamDirection.In);

            if (type2.IsSelfRelation)
            {
                id1.Name = string.Format("{0}_1ID", type1.ToString());
                id2.Name = string.Format("{0}_2ID", type1.ToString());
            }
            else
            {
                id1.Name = string.Format("{0}ID", type1.ToString());
                id2.Name = string.Format("{0}ID", type2.ToString());
            }

            id1.Direction = Parameter.ParamDirection.In;
            id2.Direction = Parameter.ParamDirection.In;

            id1.Type = Parameter.ParamType.Guid;
            id2.Type = Parameter.ParamType.Guid;

            id1.Value = obj1.ID;
            id2.Value = obj2.ID;

            List<Parameter> paramList = new List<Parameter>();

            paramList.Add(id1);
            paramList.Add(id2);

            IExtraRelationData relationData = obj2 as IExtraRelationData;

            if (relationData != null)
            {
                Storage type2child = relationData.ChildType;

                // Container class with customer data
                id2.Value = relationData.ChildID;

                if (type2.IsSelfRelation)
                {
                    /* No action. Right name already set. */
                }
                else
                {
                    id2.Name = string.Format("{0}ID", type2child.ToString());
                }

                switch (action)
                {
                    case Action.Add:
                    case Action.Update:
                        // Add extra information to be persisted
                        List<Parameter> extraData = relationData.ExtraData;
                        paramList.AddRange(extraData);
                        break;
                }
            }

            Execute(name, action, ref paramList);
        }

        /// <summary>
        /// Perfoms an action on a relationship between two objects
        /// </summary>
        /// <param name="action">Action to perform</param>
        /// <param name="obj1">First object in the relation</param>
        /// <param name="obj2">Second object in the relation</param>
        /// <param name="paramList">Input/output data to be passed to storage engine</param>
        /// <param name="revKey"></param>
        public override void Relation(Action action, IObject obj1, Storage obj2, string revKey, ref List<Parameter> paramList)
        {
            paramList.Add(new Parameter("ID", Parameter.ParamType.Guid, Parameter.ParamDirection.In, obj1.ID));

            string spName;

            if (revKey != null)
                spName = string.Format("{0}s_{1}Count", obj1.ObjType, revKey);
            else
                spName = string.Format("{0}s_{1}Count", obj1.ObjType, obj2);

            Execute(spName, action, ref paramList);
        }

        /// <summary>
        /// Implements Connection.Relation
        /// </summary>
        /// <param name="action">Action to perform</param>
        /// <param name="obj1">First object in the relation</param>
        /// <param name="type2">Type of the second object in the relation</param>
        /// <param name="creator">Delegate function to build relationship object from data</param>
        /// <param name="results">List of objects constructed using ObjectCreator function</param>
        public override void Relation(Action action, IObject obj1, Connection.Storage type2, ObjectCreator creator, out List<IObject> results)
        {
            Relation(action, obj1, null, type2, creator, out results);
        }

        /// <summary>
        /// Implements Connection.Relation
        /// </summary>
        /// <param name="action">Action to perform</param>
        /// <param name="obj1">First object in the relation</param>
        /// <param name="type2">Type of the second object in the relation</param>
        /// <param name="creator">Delegate function to build relationship object from data</param>
        /// <param name="results">List of objects constructed using ObjectCreator function</param>
        /// <param name="field">Key to be used to search for the records</param>
        public override void Relation(Action action, IObject obj1, string field, Connection.Storage type2, ObjectCreator creator, out List<IObject> results)
        {
            Storage type1 = obj1.ObjType;

            System.Diagnostics.Debug.Assert(action == Action.GetByObject);

            switch (action)
            {
                case Action.GetByObject:
                    try
                    {
                        if (type2.Flags == StorageFlags.RelationObject)
                        {
                            // Relation object
                            SqlCommand cmd = CreateCommand(null);
                            cmd.CommandType = CommandType.Text;
                            if (null != field)
                            {
                                cmd.CommandText = string.Format("SELECT {0}{1}s.* FROM {0}{1}s WHERE {3}ID = '{2}'",
                                    obj1.ObjType.Name, type2.Name, obj1.ID,
                                    field);
                            }
                            else
                            {
                                cmd.CommandText = string.Format("SELECT {0}{1}s.* FROM {0}{1}s WHERE {3}ID = '{2}'",
                                    obj1.ObjType.Name, type2.Name, obj1.ID,
                                    (type2.IsSelfRelation ? string.Format("{0}_1", obj1.ObjType.Name) : obj1.ObjType.Name));
                            }

                            List<Parameter> dummy = new List<Parameter>();
                            ReadManyInternal(cmd, Action.GetByObject, ref dummy, creator, out results);
                        }
                        else if (!string.IsNullOrEmpty(field))
                        {
                            // 1:N relationship
                            SqlCommand cmd = CreateCommand(null);
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = string.Format("SELECT {0}_Base.* FROM {0}_Base WHERE {1}ID = '{2}'",
                                type2.Name, field, obj1.ID);

                            List<Parameter> dummy = new List<Parameter>();
                            ReadManyInternal(cmd, Action.GetByObject, ref dummy, creator, out results);
                        }
                        else
                        {
                            // N:N relationship
                            string tableName = ObjectKind(obj1.ObjType, type2);
                            SqlCommand cmd = CreateCommand(null);
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = string.Format("SELECT {0}_Base.* FROM {1}s INNER JOIN {3} ON {1}s.ID = {3}.{1}ID " +
                                    "INNER JOIN {0}_Base ON {3}.{0}ID = {0}_Base.ID WHERE {1}s.ID = '{2}'",
                                type2.Name, obj1.ObjType.Name, obj1.ID, tableName);

                            List<Parameter> dummy = new List<Parameter>();
                            ReadManyInternal(cmd, Action.GetByObject, ref dummy, creator, out results);
                        }
                    }
                    catch (RecordNotFoundException)
                    {
                        // No records, return empty list
                        results = new List<IObject>();
                    }
                    break;
                default:
                    throw new NotImplementedException();
            }
        }

        /// <summary>
        /// Implements Connection.Object
        /// </summary>
        /// <param name="obj">Object type to operate on</param>
        /// <param name="action">Action to perform on the object</param>
        /// <param name="paramList">Data serialized from the object to be stored / used</param>
        public override void Object(Storage obj, Action action, ref List<Parameter> paramList)
        {
            if (action == Action.GetAll)
                action = Action.Get;

            string name = string.Format("{0}s_{1}", obj.ToString(), action.ToString());

            if (action == Action.Get || action == Action.GetByID)
            {
                ReadOne(name, action, ref paramList);
            }
            else
            {
                Execute(name, action, ref paramList);
            }
        }

        /// <summary>
        /// Implements Connection.Object.
        /// </summary>
        /// <param name="obj">Object type to operate on</param>
        /// <param name="action">Action to perform on the object</param>
        /// <param name="paramList">Data serialized from the object to be stored / used</param>
        /// <param name="creator">Delegate function to build objects from data</param>
        /// <param name="results">List of objects constructed using ObjectCreator function</param>
        public override void Object(Storage obj, Action action, ref List<Parameter> paramList,
            ObjectCreator creator, out List<IObject> results)
        {
            System.Diagnostics.Debug.Assert(action == Action.GetAll || action == Action.GetRoot
                || action == Action.GetByID || action == Action.GetByObject
                || action == Action.Search);


            results = null;

            if (action != Action.Get && action != Action.GetAll && action != Action.GetRoot && action != Action.GetByID &&
                action != Action.GetByObject && action != Action.Search)
                return;

            // Check for extended search
            if (action == Action.Search &&
                   (paramList != null && paramList.Count == 1 &&
                    paramList[0].Value != null))
            {
                if (paramList[0].Value is SearchQuery)
                {
                    ExecuteSearch(obj, paramList[0].Value as SearchQuery, creator, out results);
                }
                else if (paramList[0].Value is string)
                {
                    ExecuteSearch(obj, paramList[0].Value.ToString(), creator, out results);
                }
            }
            else
            {
                if (action == Action.GetAll)
                    action = Action.Get;

                string name = string.Format("{0}s_{1}", obj.ToString(), action.ToString());

                ReadMany(name, Action.Get, ref paramList, creator, out results);
            }
        }


        /// <summary>
        /// This method creates a cmd file and then spawns a process
        /// for executing the cmd file.
        /// </summary>
        private void ExecuteBatchJob(string dbName)
        {
            FileStream newFile = File.Create(".\\RunCmds.cmd");

            System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
            newFile.Write(encoding.GetBytes(SQLConnectionWorker.CmdString), 0, SQLConnectionWorker.CmdString.Length);
            newFile.Close();

            System.Diagnostics.Process sqlProcess = new System.Diagnostics.Process();
            sqlProcess.StartInfo.FileName = ".\\RunCmds.cmd";
            sqlProcess.StartInfo.Arguments = dbName + " /all";

            sqlProcess.Start();
            sqlProcess.WaitForExit();
        }

        /// <summary>
        /// Helper function to interact with SQL Server using Stored Procedures
        /// </summary>
        /// <param name="name">Name of stored procedure to execute</param>
        /// <param name="action">Action to execute</param>
        /// <param name="paramList">List of data to pass from/to stored procedure</param>
        private void Execute(string name, Action action, ref List<Parameter> paramList)
        {
            Dictionary<Parameter, SqlParameter> obj2sql = new Dictionary<Parameter, SqlParameter>();

            SqlCommand cmd = CreateCommand(name);
            cmd.CommandType = CommandType.StoredProcedure;

            //
            // Log and tracking
            //
            switch (action)
            {
                case Action.Add:
                    // Created(By/Date)
                    Parameter createdBy = new Parameter("CreatedBy", Parameter.ParamType.String, Parameter.ParamDirection.In, Environment.UserName);
                    Parameter createdDate = new Parameter("CreatedDate", Parameter.ParamType.DateTime, Parameter.ParamDirection.Out);
                    paramList.Add(createdBy);
                    paramList.Add(createdDate);
                    break;
                case Action.Update:
                    // Updated(By/Date)
                    Parameter updatedBy = new Parameter("UpdatedBy", Parameter.ParamType.String, Parameter.ParamDirection.In, Environment.UserName);
                    Parameter updatedDate = new Parameter("UpdatedDate", Parameter.ParamType.DateTime, Parameter.ParamDirection.Out);
                    paramList.Add(updatedBy);
                    paramList.Add(updatedDate);
                    break;
            }

            //
            // Marshal the parameters to SQL
            //
            foreach (Parameter p in paramList)
            {
                if (p.Direction != Parameter.ParamDirection.Result &&
                    p.Direction != Parameter.ParamDirection.Pipeline)
                {
                    SqlParameter param;

                    param = new SqlParameter(p.Name, paramTypeMap[p.Type]);
                    param.Direction = paramDirMap[p.Direction];

                    if (p.Value is Storage)
                        param.Value = p.Value.ToString();
                    else if (p.Value is Guid && p.Value.Equals(Guid.Empty))
                        param.Value = null;
                    else if (p.Value is DateTime && p.Value.Equals(DateTime.MinValue))
                        param.Value = null;
                    else
                        param.Value = p.Value;

                    cmd.Parameters.Add(param);

                    obj2sql.Add(p, param);
                }
            }

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                switch (e.Number)
                {
                    case 547:
                        // Object is in use by another one
                        throw new ObjectInUseException("Object in use by another object", e);
                    case 2627:
                        // Primary key violation
                        throw new RelationshipAlreadyExists("Object is already related to one another", e);
                    case 2601:
                        // Object already exists
                        throw new ObjectAlreadyExists("Object already exists", e);
                    default:
                        throw new BackendStorageException("Persistency error", e);
                }
            }
            catch (InvalidOperationException e)
            {
                throw new BackendStorageException("Persistency error", e);
            }

            //
            // Retrieve return values from SQL
            //
            foreach (Parameter p in paramList)
            {
                if (p.Direction == Parameter.ParamDirection.Out)
                {
                    // Finds the correspondent SQL match
                    SqlParameter param = obj2sql[p];

                    // Copies from SQL to our object
                    p.Value = param.Value;
                }
            }
        }

        /// <summary>
        /// Reads multiple objects from the database
        /// </summary>
        /// <param name="name">Name of stored procedure to execute</param>
        /// <param name="action">Action to perform</param>
        /// <param name="paramList">List of data to pass from/to stored procedure</param>
        /// <param name="creator">Creation function to build objects returns from stored procedure</param>
        /// <param name="results">List of objects returned from stored procedure</param>
        /// <returns>List of objects returned from stored procedure</returns>
        private List<IObject> ReadMany(string name, Action action, ref List<Parameter> paramList,
            ObjectCreator creator, out List<IObject> results)
        {
            SqlCommand cmd = CreateCommand(name);
            cmd.CommandType = CommandType.StoredProcedure;

            return ReadManyInternal(cmd, action, ref paramList, creator, out results);
        }

        private List<IObject> ReadManyInternal(SqlCommand cmd, Action action, ref List<Parameter> paramList,
            ObjectCreator creator, out List<IObject> results)
        {
            results = new List<IObject>();

            Dictionary<Parameter, SqlParameter> obj2sql = new Dictionary<Parameter, SqlParameter>();

            //
            // Marshal the parameters to SQL
            //
            foreach (Parameter p in paramList)
            {
                if (p.Direction != Parameter.ParamDirection.Result &&
                    p.Direction != Parameter.ParamDirection.Pipeline)
                {
                    SqlParameter param;

                    param = new SqlParameter(p.Name, paramTypeMap[p.Type]);
                    param.Direction = paramDirMap[p.Direction];

                    if (p.Value is Storage)
                        param.Value = p.Value.ToString();
                    else if (p.Value is Guid && p.Value.Equals(Guid.Empty))
                        param.Value = null;
                    else
                        param.Value = p.Value;

                    cmd.Parameters.Add(param);

                    obj2sql.Add(p, param);
                }
            }

            try
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (!reader.HasRows)
                    {
                        throw new RecordNotFoundException("Data not found");
                    }

                    while (reader.Read())
                    {
                        results.Add(creator(reader, ConnectionSecure));
                    }
                }
            }
            catch (SqlException e)
            {
                throw new BackendStorageException("Read error", e);
            }
            catch (InvalidOperationException e)
            {
                throw new BackendStorageException("Read error", e);
            }
            finally
            {
            }

            return results;
        }

        /// <summary>
        /// Reads a single object from the database
        /// </summary>
        /// <param name="name">Name of stored procedure to execute</param>
        /// <param name="action">Action to execute</param>
        /// <param name="paramList">List of data to pass from/to stored procedure</param>
        private void ReadOne(string name, Action action, ref List<Parameter> paramList)
        {
            Dictionary<Parameter, SqlParameter> obj2sql = new Dictionary<Parameter, SqlParameter>();

            SqlCommand cmd = CreateCommand(name);
            cmd.CommandType = CommandType.StoredProcedure;

            //
            // Marshal the parameters to SQL
            //
            foreach (Parameter p in paramList)
            {
                if (p.Direction != Parameter.ParamDirection.Result &&
                    p.Direction != Parameter.ParamDirection.Pipeline)
                {
                    SqlParameter param;

                    param = new SqlParameter(p.Name, paramTypeMap[p.Type]);
                    param.Direction = paramDirMap[p.Direction];

                    if (p.Value is Storage)
                        param.Value = p.Value.ToString();
                    else if (p.Value is Guid && p.Value.Equals(Guid.Empty))
                        param.Value = null;
                    else
                        param.Value = p.Value;

                    cmd.Parameters.Add(param);

                    obj2sql.Add(p, param);
                }
            }

            try
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (!reader.HasRows)
                    {
                        throw new RecordNotFoundException("Data not found");
                    }

                    reader.Read();

                    foreach (Parameter p in paramList)
                    {
                        if (p.Direction == Parameter.ParamDirection.Result)
                        {
                            int index = reader.GetOrdinal(p.Name);
                            p.Value = reader.GetValue(index);
                        }

                        if (p is IPipelineParameter)
                        {
                            IPipelineParameter param = p as IPipelineParameter;
                            param.Value = param.Creator(reader, ConnectionSecure);
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                throw new BackendStorageException("Read error", e);
            }
            catch (InvalidOperationException e)
            {
                throw new BackendStorageException("Read error", e);
            }
            finally
            {
            }
        }

        /// <summary>
        /// Helper function. Creates a new SqlCommand object.
        /// </summary>
        /// <param name="name">Name of stored procedure to execute</param>
        /// <returns></returns>
        private SqlCommand CreateCommand(string name)
        {
            if (conn == null)
                this.Open();

            SqlCommand newCmd = new SqlCommand(name, conn);

            SqlTransaction curTrans = CurrentTransaction;
            if (curTrans != null)
                newCmd.Transaction = curTrans;

            return newCmd;
        }

        /// <summary>
        /// Helper function. Returns the current executing transaction.
        /// </summary>
        private SqlTransaction CurrentTransaction
        {
            get
            {
                try
                {
                    lock (transactions)
                    {
                        if (transactions.Count == 0)
                            return null;

                        return transactions.Peek();
                    }
                }
                catch (InvalidOperationException)
                {
                    return null;
                }
            }
        }

        #region IConnectionAtomicity Members

        private Stack<SqlTransaction> transactions = new Stack<SqlTransaction>();

        /// <summary>
        /// Starts a new transaction with SQL Server
        /// </summary>
        void IConnectionAtomicity.BeginEdit()
        {
            SqlTransaction newTrans = conn.BeginTransaction();
            transactions.Push(newTrans);
        }

        /// <summary>
        /// Commits a running transaction with SQL Server
        /// </summary>
        void IConnectionAtomicity.EndEdit()
        {
            SqlTransaction currentTrans = transactions.Pop();
            currentTrans.Commit();
        }

        /// <summary>
        /// Rollbacks a running transaction with SQL Server
        /// </summary>
        void IConnectionAtomicity.CancelEdit()
        {
            SqlTransaction currentTrans = transactions.Pop();
            currentTrans.Rollback();
        }

        #endregion

        internal string[] GetDatabaseList(string login)
        {
            List<string> result = new List<string>();
            string[] systemDBs = new string[] {
                "master",
                "model",
                "msdb",
                "tempdb",
            };
            List<string> systemDBsList = new List<string>(systemDBs);

            SqlCommand cmd = CreateCommand("sp_databases");
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    string dbName = reader.GetString(reader.GetOrdinal("DATABASE_NAME"));
                    if (!result.Contains(dbName) && !systemDBsList.Contains(dbName))
                        result.Add(dbName);
                }
            }
            catch (SqlException)
            {
                return null;
            }

            return result.ToArray();
        }

        internal static string ObjectKind(Connection.Storage obj1, Connection.Storage obj2)
        {
            // Step 0 : Find right relationship table
            if (obj1 != obj2 && BindingMap.Contains(string.Format("{0}-{1}", obj2, obj1)))
            {
                // We are viewing the problem in "reverse". Flip the table
                return ObjectKind(obj2, obj1);
            }

            string objKind = string.Format("{0}{1}s", obj1, obj2 == null ? "" : obj2.ToString());
            return objKind;
        }

        /// <summary>
        /// Copy connection string from the source 
        /// </summary>
        /// <param name="src"></param>
        protected override void CopyFrom(Connection src)
        {
            SQLConnectionWorker conn = (SQLConnectionWorker)src;
            this.connString = conn.connString;
        }

        /// <summary>
        /// Method recreates the SQLConnectionWorker from serialized SQLConnectionWorker 
        /// </summary>
        /// <param name="info"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        public static SQLConnectionWorker Create(SerializationInfo info, StreamingContext context)
        {
            return new SQLConnectionWorker(info.GetString(ConnectionString));    
        }

        #region IConnectionProviderInfo Members

        string IConnectionProviderInfo.Name
        {
            get { return Properties.Resources.ProviderSQLServer; }
        }

        Version IConnectionProviderInfo.Version
        {
            get { return new Version(SQLConnectionWorker.CurrentVersion); }
        }

        System.Drawing.Image IConnectionProviderInfo.IconSmall
        {
            get { return Properties.Resources.SQLLogo_Small; }
        }

        System.Drawing.Image IConnectionProviderInfo.IconLarge
        {
            get { return Properties.Resources.SQLLogo; }
        }

        #endregion

        #region IStorageSecurity Members

        public ISystemSecurityPolicy SystemPolicy
        {
            get
            {
                // Not supported.
                return null;
            }
        }

        /// <summary>
        /// Grants access to the principal to the storage.
        /// </summary>
        /// <param name="principal"></param>
        /// <param name="role"></param>
        public void GrantDirectAccess(ISecurityPrincipal principal, StorageRole role)
        {
            foreach (ISecurityCredential cred in principal.Credentials)
            {
                switch (cred.Type)
                {
                    case CredentialType.Windows:
                        {
                            CreateLogin(cred.Credential);
                            CreateUser(cred.Credential);
                            AssignDBRole(cred, role);
                        }
                        break;
                }
            }
        }

        /// <summary>
        /// Revoke security principal role access from the storage
        /// </summary>
        /// <param name="principal"></param>
        /// <param name="role"></param>
        public void RevokeDirectAccess(ISecurityPrincipal principal, StorageRole role)
        {
            foreach (ISecurityCredential cred in principal.Credentials)
            {
                switch (cred.Type)
                {
                    case CredentialType.Windows:
                        {
                            RevokeDBRole(cred, role);
                        }
                        break;
                }
            }
        }

        /// <summary>
        /// Checks if the security principal has a specific role permission
        /// </summary>
        /// <param name="principal"></param>
        /// <param name="role"></param>
        /// <returns></returns>
        public bool CheckDirectAccess(ISecurityPrincipal principal, StorageRole role)
        {
            // Not supported.
            return false;
        }

        /// <summary>
        /// Returns all permissions of the security principal to the store
        /// </summary>
        /// <param name="principal"></param>
        /// <returns></returns>
        public StorageRole[] Get(ISecurityPrincipal principal)
        {
            // Not supported.
            return null;
        }

        /// <summary>
        /// Returns all permissions granted to the store
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        public Dictionary<ISecurityPrincipal, StorageRole[]> Permissions
        {
            get
            {
                // Not supported.
                return new Dictionary<ISecurityPrincipal, StorageRole[]>();
            }
        }

        /// <summary>
        /// Assigns the DB role based on the role of the user.
        /// </summary>
        /// <param name="credential">The credential.</param>
        /// <param name="role">The role.</param>
        private void AssignDBRole(ISecurityCredential credential, StorageRole role)
        {
            Parameter roleParam = new Parameter("rolename", Parameter.ParamType.Text, Parameter.ParamDirection.In);
            Parameter memberParam = new Parameter("membername", Parameter.ParamType.Text, Parameter.ParamDirection.In);

            switch (role)
            {
                case StorageRole.Administrator:
                default:
                    roleParam.Value = DBOwner;
                    break;
            }

            memberParam.Value = credential.Credential;

            List<Parameter> parameters = new List<Parameter>();
            parameters.Add(roleParam);
            parameters.Add(memberParam);

            Execute(SPAddrolemember, Action.Execute, ref parameters);
        }

        /// <summary>
        /// Removes the DB role.
        /// </summary>
        /// <param name="credential">The credential.</param>
        /// <param name="role">The role.</param>
        private void RevokeDBRole(ISecurityCredential credential, StorageRole role)
        {
            Parameter roleParam = new Parameter("rolename", Parameter.ParamType.Text, Parameter.ParamDirection.In);
            Parameter memberParam = new Parameter("membername", Parameter.ParamType.Text, Parameter.ParamDirection.In);

            switch (role)
            {
                case StorageRole.Administrator:
                default:
                    roleParam.Value = DBOwner;
                    break;
            }

            memberParam.Value = credential.Credential;

            List<Parameter> parameters = new List<Parameter>();
            parameters.Add(roleParam);
            parameters.Add(memberParam);

            Execute(SPDroprolemember, Action.Delete, ref parameters);
        }

        /// <summary>
        /// Creates the login in the SQL server.
        /// </summary>
        /// <param name="loginName">Name of the login.</param>
        private void CreateLogin(string loginName)
        {
            Parameter loginNameParam = new Parameter("loginame", Parameter.ParamType.Text, Parameter.ParamDirection.In);
            loginNameParam.Value = loginName;

            List<Parameter> parameters = new List<Parameter>();
            parameters.Add(loginNameParam);

            Execute(SPCreateLogin, Action.Execute, ref parameters);
        }

        /// <summary>
        /// Creates the user in the Database.
        /// </summary>
        /// <param name="userName">Name of the user.</param>
        private void CreateUser(string userName)
        {
            Parameter userNameParam = new Parameter("loginame", Parameter.ParamType.Text, Parameter.ParamDirection.In);
            userNameParam.Value = userName;

            List<Parameter> parameters = new List<Parameter>();
            parameters.Add(userNameParam);

            Execute(SPCreateUser, Action.Execute, ref parameters);
        }

        #endregion

        #region ISerializable Members
        /// <summary>
        /// Overrided serialization implementation
        /// </summary>
        /// <param name="info"></param>
        /// <param name="context"></param>
        public override void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue(ConnectionString, this.connString);
        }

        #endregion
    }
}
